package com.skhu.as.dao;

import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

import com.skhu.as.domain.DutyHours;
import com.skhu.as.domain.FaultHandling;
import com.skhu.as.domain.FaultHandlingDomain;
import com.skhu.as.domain.PresentCondition;
import com.skhu.as.domain.PresentConditionDomain;
import com.skhu.as.domain.Worker;
import com.skhu.as.mapper.DutyHoursM;
import com.skhu.as.mapper.FaultHandlingDomainM;
import com.skhu.as.mapper.FaultHandlingM;
import com.skhu.as.mapper.PresentConditionDomainM;
import com.skhu.as.mapper.PresentConditionM;
import com.skhu.as.mapper.WorkerM;

public class IndexD {
	
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<FaultHandling> getUntreatedWork(){
		String sql = "SELECT " +
						"fault_handling_no, " + 
						"receptionist_no, " + 
						"duty_hours_no, " + 
						"request_type_no, " + 
						"requester_no, " + 
						"first_worker_no, " + 
						"process_status_no, " +
						"receipt_date, " + 
						"process_date, " + 
						"request_content, " +
						"processing_information " + 
					"FROM  " +
						"fault_handling " +
					"WHERE " +
						"process_status_no = 1 " +
					"ORDER BY " +
						"process_date ASC";
		List<FaultHandling> untreatedWork = jdbcTemplate.query(sql, new FaultHandlingM());
		return untreatedWork;
	}
	
	
	public List<DutyHours> getDutyHoursList(){
		String sql = "SELECT duty_hours_no, duty_hours FROM duty_hours";
		List<DutyHours> dutyHoursList = jdbcTemplate.query(sql, new DutyHoursM());
		return dutyHoursList;
	}
	
	public List<FaultHandling> getFaultHandlingByDate(String dateStr){
		String sql = "SELECT " +
						"fault_handling_no, " +
						"receptionist_no, " +
						"duty_hours_no, " +
						"request_type_no, " +
						"requester_no, " +
						"first_worker_no, " +
						"process_status_no, " +
						"receipt_date, " +
						"process_date, " +
						"request_content, " +
						"processing_information " +
					"FROM " +
						"fault_handling " +
					"WHERE " +
						"process_date LIKE '" + dateStr + "%'";
		
		List<FaultHandling> faultHandlingList = jdbcTemplate.query(sql, new FaultHandlingM());
		
		return faultHandlingList;
	}
	
	public List<FaultHandlingDomain> getFaultHandlingDomainByNo(String dateStr, String no){
		
		String sql = "SELECT " +
				"f.fault_handling_no, " +
				"rec.worker_no receptionist_no, " +
				"rec.name receptionist_name, " +
				"rec.major receptionist_major, " +
				"rec.contact_number receptionist_contact_number, " +
				"d.duty_hours_no, " +
				"d.duty_hours, " +
				"rt.request_type_no, " +
				"rt.request_type, " +
				"r.requester_no, " +
				"r.name requester_name, " +
				"r.contact_number requester_contact_number, " +
				"r.request_place, " +
				"r.department requester_department, " +
				"fw.worker_no first_worker_no, " +
				"fw.name first_worker_name, " +
				"fw.major first_worker_major, " +
				"fw.contact_number first_worker_contact_number, " +
				"ps.process_status_no, " +
				"ps.process_status, " +
				"f.receipt_date, " +
				"f.process_date, " +
				"f.request_content, " +
				"f.processing_information " +
			"FROM " +
				"fault_handling f, worker rec, duty_hours d, request_type rt, requester r, worker fw, process_status ps " +
			"WHERE " +
				"f.duty_hours_no = d.duty_hours_no AND " +
				"f.receptionist_no = rec.worker_no AND " +
				"f.request_type_no = rt.request_type_no AND " +
				"f.requester_no = r.requester_no AND " +
				"f.first_worker_no = fw.worker_no AND " +
				"f.process_status_no = ps.process_status_no AND " +
				"f.process_date = ? AND " +
				"f.fault_handling_no = ?";

			Object[] param = new Object[]{
				dateStr,
				no
			};
			
			List<FaultHandlingDomain> faultHandlingDomainList = jdbcTemplate.query(sql, param, new FaultHandlingDomainM());
			return faultHandlingDomainList;
		
	}
	
	public List<FaultHandlingDomain> getFaultHandlingDomainByDate(String dateStr){
		
		String sql = "SELECT " +
							"f.fault_handling_no, " +
							"rec.worker_no receptionist_no, " +
							"rec.name receptionist_name, " +
							"rec.major receptionist_major, " +
							"rec.contact_number receptionist_contact_number, " +
							"d.duty_hours_no, " +
							"d.duty_hours, " +
							"rt.request_type_no, " +
							"rt.request_type, " +
							"r.requester_no, " +
							"r.name requester_name, " +
							"r.contact_number requester_contact_number, " +
							"r.request_place, " +
							"r.department requester_department, " +
							"fw.worker_no first_worker_no, " +
							"fw.name first_worker_name, " +
							"fw.major first_worker_major, " +
							"fw.contact_number first_worker_contact_number, " +
							"ps.process_status_no, " +
							"ps.process_status, " +
							"f.receipt_date, " +
							"f.process_date, " +
							"f.request_content, " +
							"f.processing_information " +
						"FROM " +
							"fault_handling f, worker rec, duty_hours d, request_type rt, requester r, worker fw, process_status ps " +
						"WHERE " +
							"f.duty_hours_no = d.duty_hours_no AND " +
							"f.receptionist_no = rec.worker_no AND " +
							"f.request_type_no = rt.request_type_no AND " +
							"f.requester_no = r.requester_no AND " +
							"f.first_worker_no = fw.worker_no AND " +
							"f.process_status_no = ps.process_status_no AND " +
							"f.process_date = ?";
		
		Object[] param = new Object[]{
				dateStr
		};
		
		List<FaultHandlingDomain> faultHandlingDomainList = jdbcTemplate.query(sql, param, new FaultHandlingDomainM());
		return faultHandlingDomainList;
		
	}
	
	public List<Worker> getWorkerList(){
		String sql = "SELECT worker_no, name, major, contact_number, use FROM worker WHERE use = 'use'";
		List<Worker> workerList = jdbcTemplate.query(sql, new WorkerM());
		return workerList;
	}
	
	public List<PresentConditionDomain> getPresentConditionListByList(int[] faultHandlingNoList){
		String sql = "SELECT pc.present_condition_no, pc.fault_handling_no, w.name, w.contact_number, pc.date, pc.content FROM present_condition pc, worker w WHERE pc.worker_no = w.worker_no AND ";
		Object[] param = new Object[faultHandlingNoList.length];
		
		sql = sql + "fault_handling_no IN (";
		
		for(int i=0; i<faultHandlingNoList.length; i++){
			sql = sql + "? ";
			if(i < faultHandlingNoList.length-1){
				sql = sql + ", ";
			}
			param[i] = faultHandlingNoList[i];
		}
		
		sql = sql + ")";
		
		 //ORDER BY date DESC";
		List<PresentConditionDomain> presentConditionList = jdbcTemplate.query(sql, param, new PresentConditionDomainM());
		 
		return presentConditionList;
	}
	
	public List<PresentConditionDomain> getPresentConditionList(String faultHandlingNo){
		String sql = "SELECT pc.present_condition_no, pc.fault_handling_no, w.name, w.contact_number, pc.date, pc.content FROM present_condition pc, worker w WHERE pc.worker_no = w.worker_no AND fault_handling_no IN(" + faultHandlingNo + ")";
		 //ORDER BY date DESC";
		List<PresentConditionDomain> presentConditionList = jdbcTemplate.query(sql, new PresentConditionDomainM());
		 
		return presentConditionList;
	}
}
